package cn.yutong.system.controller;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import cn.yutong.framework.RestResponse;
import cn.yutong.framework.util.TimeTools;
import cn.yutong.system.entity.EnvIndicators;
import cn.yutong.system.service.IEnvIndicatorsService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

@RestController
@Api(value = "表格数据(上传)", tags = "表格数据(上传)")
public class ExcelFileUpload {
	static Logger logger = LogManager.getLogger(ExcelFileUpload.class);
	@Autowired
	IEnvIndicatorsService envindicatorsServiceImpl;

	@ApiOperation("文件上传到服务器,执行解析(form-data格式，key为file,value为要上传的文件)")
	@PostMapping("/upload/excel")
	public RestResponse uploadLocal(HttpServletRequest request) throws Exception {
		// List<MultipartFile> files = ((MultipartHttpServletRequest)
		// request).getFiles("file");
		// Map<String, Object> uploadResult =
		// fileStorageService.uploadLocalFiles(files);
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		MultipartFile file = multipartRequest.getFile("file");
		if (file.isEmpty()) {
			return RestResponse.newEntity(401, "文件不能为空", null);
		}
		InputStream inputStream = file.getInputStream();
		List<List<Object>> excelList = getDataListByExcel(inputStream, file.getOriginalFilename());
		inputStream.close();
		// 转换数据 为list<map>
		List<Map<String, Object>> dataListMap = convertDataList(excelList);

		List<EnvIndicators> envIndicators = new ArrayList<>();
		for (Map<String, Object> map : dataListMap) {
			// "time", "atmosphereTemp", "atmosphereHumidity", "soilHumidity", "soilTemp",
			// "rainfall","rainfallDay", "windSpeed", "windDirection", "illuminance" };
			// "上传时间", "大气温度", "大气湿度", "土湿", "土温", "雨量", "日雨量","风速", "风向", "照度"
			EnvIndicators env = new EnvIndicators();
			Date time = TimeTools.strDateParse(String.valueOf(map.get("time")), "yyyy-MM-dd HH:mm:ss");
			env.setTime(time);
			env.setTimestamp((int) (time.getTime() / 1000));
			env.setAtmosphereTemp(new Double(String.valueOf(map.get("atmosphereTemp"))));
			env.setAtmosphereHumidity(new Double(String.valueOf(map.get("atmosphereHumidity"))));
			env.setSoilHumidity(new Double(String.valueOf(map.get("soilHumidity"))));
			env.setSoilTemp(new Double(String.valueOf(map.get("soilTemp"))));
			env.setRainfall(new Double(String.valueOf(map.get("rainfall"))));
			env.setRainfallDay(new Double(String.valueOf(map.get("rainfallDay"))));
			env.setWindSpeed(new Double(String.valueOf(map.get("windSpeed"))));
			env.setWindDirection(new Double(String.valueOf(map.get("windDirection"))));
			env.setIlluminance(new Double(String.valueOf(map.get("illuminance"))));
			envIndicators.add(env);
		}

		envindicatorsServiceImpl.insertBatch(envIndicators);

		return RestResponse.newEntity(200, "文件上传完成,共添加：" + envIndicators.size() + " 行", file.getOriginalFilename());
	}

	private List<Map<String, Object>> convertDataList(List<List<Object>> excelList) {
		// 处理标题行
		Map<Integer, String> headInx = getColumnIndex(excelList.get(0));

		List<Map<String, Object>> dataListMap = new ArrayList<>();
		for (int i = 1; i < excelList.size(); i++) {
			List<Object> lo = excelList.get(i);
			// 第一行为标题行：[上传时间, 大气温度, 大气湿度, 土湿, 日雨量, 雨量, 风速, 风向, 土温, 照度]
			Map<String, Object> row = new LinkedHashMap<String, Object>();
			for (int x = 0; x < lo.size(); x++) {
				row.put(headInx.get(x), lo.get(x));
			}
			dataListMap.add(row);

		}
		return dataListMap;
	}

	private Map<Integer, String> getColumnIndex(List<Object> headers) {

		// [上传时间, 大气温度, 大气湿度, 土湿, 日雨量, 雨量, 风速, 风向, 土温, 照度]
		String[] titleArr = { "time", "atmosphereTemp", "atmosphereHumidity", "soilHumidity", "soilTemp", "rainfall",
				"rainfallDay", "windSpeed", "windDirection", "illuminance" };
		String[] titleArrCN = { "上传时间", "大气温度", "大气湿度", "土湿", "土温", "雨量", "日雨量", "风速", "风向", "照度" };
		Map<Integer, String> indexs = new HashMap<>();
		for (int i = 0; i < headers.size(); i++) {
			String head = String.valueOf(headers.get(i));
			for (int j = 0; j < titleArrCN.length; j++) {
				if (head.equals(titleArrCN[j])) {
					indexs.put(i, titleArr[j]);
				}
			}
		}
		return indexs;
	}

	public static String readAsChars(HttpServletRequest request) {
		BufferedReader br = null;
		StringBuilder sb = new StringBuilder("");
		try {
			br = request.getReader();
			String str;
			while ((str = br.readLine()) != null) {
				sb.append(str);
			}
			br.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (null != br) {
				try {
					br.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return sb.toString();
	}

	/**
	 * 处理上传的文件
	 *
	 * @param in
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public List<List<Object>> getDataListByExcel(InputStream in, String fileName) throws Exception {
		List<List<Object>> list = new ArrayList<>();
		// 创建Excel工作薄
		Workbook work = this.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if (row == null) {
					// if (row == null || row.getFirstCellNum() == j) {
					continue;
				}

				List<Object> li = new ArrayList<>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					li.add(cell);
				}
				list.add(li);
			}
		}
		work.close();
		return list;
	}

	/**
	 * 判断文件格式
	 *
	 * @param inStr
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook workbook = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(fileType)) {
			workbook = new HSSFWorkbook(inStr);
		} else if (".xlsx".equals(fileType)) {
			workbook = new XSSFWorkbook(inStr);
		} else {
			throw new Exception("请上传excel文件！");
		}
		return workbook;
	}
}
